<?php

use yii\db\Schema;
use yii\db\Migration;

class m151124_064052_initial_database extends Migration
{
    public function up()
    {
        $this->initialArticle();
        $this->initialCategory();
        $this->initialArticleCategory();
        $this->initialKeyword();
        $this->initialArticleKeyword();
        $this->initialComment();

        $this->initialForeignKey();
    }

    public function down()
    {
        echo "m151124_064052_initial_database will be reverted.\n";
        $this->dropTable('article_category');
        $this->dropTable('article_keyword');
        $this->dropTable('article');
        $this->dropTable('category');
        $this->dropTable('keyword');
        $this->dropTable('comment');
        echo "m151124_064052_initial_database has been reverted.\n";

        return true;
    }

    private function initialArticle()
    {
        $this->createTable('article', [
            'id' => $this->primaryKey(),
            'pubtime' => $this->timestamp()->notNull() . " NOT NULL COMMENT '发布时间'",
            'title' => $this->string(255)->notNull(),
            'content' => $this->text()->notNull(),
            'origin' => $this->string(255)->notNull(),
            'clicked' => $this->integer(11)->notNull(),
            'like' => $this->integer(11)->notNull(),
            'status' => $this->smallInteger(1)->notNull(),
            'commentary' => $this->smallInteger(1)->notNull(),
        ], 'ENGINE=InnoDB DEFAULT CHARSET=utf8');

        $columns = ['pubtime', 'title', 'content', 'origin', 'clicked', 'like', 'status', 'commentary'];
        $rows = [
            [date('Y-m-d H:i:s'), '一条记录', '第一条记录的内容', '原创', 1000, 888, 0, 0],
            [date('Y-m-d H:i:s', time() + 15), '二条记录', '第一条记录的内容', '原创', 1000, 888, 0, 0],
            [date('Y-m-d H:i:s', time() + 25), '三条记录', '第一条记录的内容', '原创', 1000, 888, 0, 0],
        ];

        $this->batchInsert('article', $columns, $rows);
    }

    private function initialCategory()
    {
        $this->createTable('category', [
            'id' => $this->primaryKey(),
            'category' => $this->string(255)->notNull(),
            'pcid' => $this->integer(11)
        ], 'ENGINE=InnoDB DEFAULT CHARSET=utf8');

        $columns = ['category', 'pcid'];
        $rows = [
            ['Memcached', 0],
            ['Linux', 0],
            ['MySQL', 0],
            ['PHP', 0],
            ['Debian', 2],
            ['Fedora', 2],
            ['Archlinux', 2],
            ['Slackware', 2],
            ['Gentoo', 2],
            ['Mariadb', 3],
        ];

        $this->batchInsert('category', $columns, $rows);
    }

    private function initialKeyword()
    {
        $this->createTable('keyword', [
            'id' => $this->primaryKey(),
            'keyword' => $this->string(255)->notNull(),
        ], 'ENGINE=InnoDB DEFAULT CHARSET=utf8');

        $columns = ['keyword'];
        $rows = [
            ['Memcached'],
            ['Linux'],
            ['MySQL'],
            ['PHP'],
            ['Debian'],
            ['Fedora'],
            ['Archlinux'],
            ['Slackware'],
            ['Gentoo'],
            ['Mariadb'],
        ];

        $this->batchInsert('keyword', $columns, $rows);
    }

    private function initialComment()
    {
        $this->createTable('comment', [
            'id' => $this->primaryKey(),
            'commenter' => $this->string(255)->notNull(),
            'email' => $this->string(255)->notNull(),
            'pcid' => $this->integer(11),
            'pubtime' => $this->timestamp(),
            'content' => $this->text(),
            'aid' => $this->integer(11),
            'subscribe' => $this->smallInteger(1)
        ], 'ENGINE=InnoDB DEFAULT CHARSET=utf8');

        $columns = ['commenter', 'email', 'pcid', 'pubtime', 'content', 'aid', 'subscribe'];
        $rows = [
            ['sidgwick', '1005411480a@gmail.com', 0, date('Y-m-d H:i:s'), '非常好的一篇文章', 1, 1],
            ['sidgwick', '1005411480a@gmail.com', 1, date('Y-m-d H:i:s', time() + 60), '确实是很好的文章', 1, 1],
            ['sidgwick', '1005411480a@gmail.com', 0, date('Y-m-d H:i:s'), '非常好的一篇文章', 2, 1],
            ['sidgwick', '1005411480a@gmail.com', 0, date('Y-m-d H:i:s'), '非常好的一篇文章', 3, 1],
        ];

        $this->batchInsert('comment', $columns, $rows);
    }

    private function initialArticleCategory()
    {
        $this->createTable('article_category', [
            'aid' => $this->integer(11),
            'cid' => $this->integer(11)
        ], 'ENGINE=InnoDB DEFAULT CHARSET=utf8');

        $columns = ['aid', 'cid'];
        $rows = [
            [1, 1],
            [1, 2],
            [1, 5],
            [2, 1],
            [2, 2],
            [2, 3],
            [2, 4],
            [3, 3],
            [3, 5]
        ];

        $this->batchInsert('article_category', $columns, $rows);
    }

    private function initialArticleKeyword()
    {
        $this->createTable('article_keyword', [
            'aid' => $this->integer(11),
            'kid' => $this->integer(11)
        ], 'ENGINE=InnoDB DEFAULT CHARSET=utf8');

        $columns = ['aid', 'kid'];
        $rows = [
            [1, 1],
            [1, 2],
            [1, 5],
            [2, 1],
            [2, 2],
            [2, 3],
            [2, 4],
            [3, 3],
            [3, 5]
        ];

        $this->batchInsert('article_keyword', $columns, $rows);
    }

    private function initialForeignKey()
    {
        $this->createIndex('article_category', 'article_category', ['aid', 'cid']);
        $this->createIndex('article_keyword', 'article_keyword', ['aid', 'kid']);
        $this->addForeignKey('article_category_aid', 'article_category', 'aid', 'article', 'id');
        $this->addForeignKey('article_category_cid', 'article_category', 'cid', 'category', 'id');
        $this->addForeignKey('article_keyword_aid', 'article_keyword', 'aid', 'article', 'id');
        $this->addForeignKey('article_keyword_cid', 'article_keyword', 'kid', 'keyword', 'id');
        $this->addForeignKey('article_comment', 'comment', 'aid', 'article', 'id');
    }
}
